﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CSM_SQL.DAL
{
    public class GoodsDAL
    {
        CSMDataBaseDataContext _data;
        CSMDataBaseDataContext data 
        {
            get{
                if(_data == null || !_data.DatabaseExists())
                    _data = new CSMDataBaseDataContext("Data Source=.;Initial Catalog=SuperMarket;Integrated Security=True");
                return _data;
            }
        }

        internal List<Goods> All()
        {
            //List<Goods> all = new List<Goods>();
            return data.Goods.ToList<Goods>();
        }

        internal Boolean Add(Goods item)
        {
            try
            {

                var temp = from g in data.Goods select g.G_ID;
                if (!(temp.Count() > 0))
                    item.G_ID = 0;
                else
                    item.G_ID = temp.Max() + 1;
                data.Goods.InsertOnSubmit(item);
                data.SubmitChanges();
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }

        internal Boolean Edit(Goods item)
        {
            try
            {
                var target = (from g in data.Goods where g.G_ID == item.G_ID select g).ToList()[0];
                target.Name = item.Name;
                target.Img = item.Img;
                target.Price = item.Price;
                target.Remark = item.Remark;
                target.Stock = item.Stock;
                target.Type = item.Type;
                target.Weight = item.Weight;
                data.SubmitChanges();
            }
            catch (Exception ex)
            {
                return false;
                throw (ex);
            }
            return true;
        }

        internal Boolean Delete(int ID)
        {
            try
            {
                var target = (from g in data.Goods where g.G_ID == ID select g).ToList()[0];
                data.Goods.DeleteOnSubmit(target);
                data.SubmitChanges();
            }
            catch (Exception ex)
            {
                return false;
                throw (ex);
            }
            return true;

        }

        internal List<ShowGoods> ShowGoods()
        {
            var li = data.Goods.Where(g => g.Stock > 0);
            List<ShowGoods> list = new List<ShowGoods>();
            foreach(var l in li)
            {
                ShowGoods a = new ShowGoods
                {
                    id = l.G_ID,
                    name = l.Name,
                    type = l.Type,
                    detail = l.Detail,
                    price = l.Price,
                    img = l.Img
                };
                var ord = (from q in data.Order.Where(o => o.G_ID == a.id && o.State >= 3) join ass in data.Assess on q.ID equals ass.ID select ass.Grade).Average() ;
                if (ord >= 0)
                    a.grade = Convert.ToUInt16(ord);
                else
                    a.grade = 5;
                list.Add(a);
            }
            return list;
        }

        internal Boolean InStore(int G_ID,int num)
        {
            try
            {
                data.Goods.Where(g => g.G_ID == G_ID).First().Stock += num;
                data.SubmitChanges();
            }
            catch(Exception ex)
            {

            }
            return true;
        }
    }
}
